package com.sifeng.test;

import com.sifeng.pojo.RequirementsDTO;
import com.sifeng.pojo.WorkloadRatioDTO;
import com.sifeng.utils.MyUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @ClassName FillInEstimatedWorkload
 * @Author yang_gsh
 * @Date 2019/04/29 13:45
 * @Description 填写预计工作量
 */
public class FillInEstimatedWorkload {
    public static Map<String, RequirementsDTO> getRequirementsList(String path) throws IOException {
        Map<String, RequirementsDTO> map = new HashMap<String, RequirementsDTO>();

        XSSFWorkbook wb = MyUtils.getXSSFWorkbook(path);
        XSSFSheet sheet = wb.getSheetAt(0);
        List<WorkloadRatioDTO> list = new ArrayList<WorkloadRatioDTO>();
        if (sheet == null) {
            return null;
        }

        for(int i = 1; i < sheet.getLastRowNum(); i++) {
            XSSFRow row = sheet.getRow(i);
            XSSFCell cell_A = row.getCell(0);
            if(cell_A == null) {
                continue;
            }
            //cell_A.setCellType(Cell.CELL_TYPE_STRING);
            String requirementNumber = cell_A.getStringCellValue();

            if("".equals(requirementNumber.trim())) {
                continue;
            }

            XSSFCell cell_B = row.getCell(1);
            XSSFCell cell_D = row.getCell(3);
            cell_D.setCellType(Cell.CELL_TYPE_STRING);
            XSSFCell cell_F = row.getCell(4);
            cell_F.setCellType(Cell.CELL_TYPE_STRING);

            map.put(requirementNumber.trim(),
                    setRequirementsDTO(requirementNumber.trim(), cell_B.getStringCellValue(),
                            Integer.parseInt(cell_D.getStringCellValue()), Integer.parseInt(cell_F.getStringCellValue())));
        }

        XSSFSheet sheet3 = wb.getSheetAt(2);
        if (sheet3 == null) {
            return map;
        }

        for(int i = 1; i < sheet3.getLastRowNum(); i++) {
            XSSFRow row = sheet3.getRow(i);
            XSSFCell cell_A = row.getCell(0);
            if(cell_A == null) {
                continue;
            }
            String requirementNumber = cell_A.getStringCellValue();

            if("".equals(requirementNumber.trim())) {
                continue;
            }

            XSSFCell cell_B = row.getCell(1);
            XSSFCell cell_D = row.getCell(3);
            cell_D.setCellType(Cell.CELL_TYPE_STRING);
            XSSFCell cell_J = row.getCell(9);
            cell_J.setCellType(Cell.CELL_TYPE_STRING);

            map.put(requirementNumber.trim(),
                    setRequirementsDTO(requirementNumber.trim(), cell_B.getStringCellValue(),
                            Integer.parseInt(cell_D.getStringCellValue()), Integer.parseInt(cell_J.getStringCellValue())));
        }

        return map;
    }

    private static RequirementsDTO setRequirementsDTO(String requirementNumber, String requirementName,
                                                      int totalWorkload, int completeTheWorkloadThisMonth) {

        RequirementsDTO requirementsDTO = new RequirementsDTO();
        requirementsDTO.setRequirementNumber(requirementNumber);
        requirementsDTO.setRequirementName(requirementName);
        requirementsDTO.setTotalWorkload(totalWorkload);
        requirementsDTO.setCompleteTheWorkloadThisMonth(completeTheWorkloadThisMonth);

        return requirementsDTO;
    }

    public static void writeExcel(String path, Map<String, RequirementsDTO> map) throws IOException {
        XSSFWorkbook wb = MyUtils.getXSSFWorkbook(path);
        XSSFSheet sheet = wb.getSheetAt(0);
        if (sheet == null) {
            return;
        }

        for (int i = 1; i <= sheet.getLastRowNum(); i++)  {
            XSSFRow row = sheet.getRow(i);
            if(row == null) {
                continue;
            }
            XSSFCell cellA = row.getCell(0);
            if(cellA == null) {
                continue;
            }
            cellA.setCellType(Cell.CELL_TYPE_STRING);
            String requirementNumber = cellA.getStringCellValue();
            if(map.containsKey(requirementNumber)) {
                RequirementsDTO requirementsDTO = map.get(requirementNumber);

                XSSFCell cellI = row.getCell(8);  //牵头部门
                cellI.setCellValue("营销服务研发部");
                cellI.setCellType(Cell.CELL_TYPE_STRING);

                XSSFCell cellK = row.getCell(10);  //牵头部门
                cellK.setCellValue(requirementsDTO.getCompleteTheWorkloadThisMonth());
                cellK.setCellType(Cell.CELL_TYPE_NUMERIC);
            } else {
                continue;
            }
        }

        FileOutputStream outputStream = new FileOutputStream(path);

        wb.write(outputStream);
        outputStream.flush();
        wb.close();
    }

    public static void main(String[] args) throws IOException {
        String readPath = "E:\\hisun\\开发三部\\功能点评估表\\201904\\需求列表_201904.xlsx";
        String writePath = "E:\\hisun\\开发三部\\功能点评估表\\201904\\需求开发管理开发报表.xlsx";
        Map<String, RequirementsDTO> map = FillInEstimatedWorkload.getRequirementsList(readPath);
        FillInEstimatedWorkload.writeExcel(writePath, map);
    }
}
